const fs = require('fs');
const path = require('path');
const XLSX = require('xlsx');
const Database = require('better-sqlite3');

// Database connection
const DB_PATH = path.join(process.cwd(), 'data', 'delivery-management.db');
const db = new Database(DB_PATH);

/**
 * Parse Excel file and extract headers
 */
function parseExcelFile(filePath) {
  try {
    const buffer = fs.readFileSync(filePath);
    const workbook = XLSX.read(buffer, { type: 'buffer' });
    const filename = path.basename(filePath);
    
    const sheets = workbook.SheetNames.map(sheetName => {
      const worksheet = workbook.Sheets[sheetName];
      const jsonData = XLSX.utils.sheet_to_json(worksheet, { 
        header: 1,
        defval: null,
        raw: false
      });
      
      const headers = jsonData[0] || [];
      const rows = jsonData.slice(1);
      
      return {
        sheetName,
        headers: headers.map(h => h?.toString() || '').filter(h => h.trim() !== ''),
        rows
      };
    });
    
    return { filename, sheets };
  } catch (error) {
    console.error(`Error parsing Excel file ${filePath}:`, error.message);
    return null;
  }
}

/**
 * Get all table_c tables from database
 */
function getDatabaseTables() {
  try {
    const stmt = db.prepare(`
      SELECT name FROM sqlite_master 
      WHERE type='table' AND name LIKE 'table_c%'
      ORDER BY name
    `);
    return stmt.all();
  } catch (error) {
    console.error('Error getting database tables:', error.message);
    return [];
  }
}

/**
 * Get column names for a table
 */
function getTableColumns(tableName) {
  try {
    const stmt = db.prepare(`PRAGMA table_info(${tableName})`);
    const columns = stmt.all();
    return columns
      .filter(col => !['id', 'created_at', 'updated_at'].includes(col.name))
      .map(col => col.name);
  } catch (error) {
    console.error(`Error getting columns for table ${tableName}:`, error.message);
    return [];
  }
}

/**
 * Check if two field names are exactly the same
 */
function isExactMatch(excel, database) {
  return excel === database;
}

/**
 * Check if two field names are close matches
 */
function isCloseMatch(excel, database) {
  // Remove spaces and normalize
  const normalize = (field) => {
    return field
      .replace(/\s+/g, '')
      .replace(/[_\-]/g, '')
      .toLowerCase();
  };
  
  const normalizedExcel = normalize(excel);
  const normalizedDb = normalize(database);
  
  if (normalizedExcel === normalizedDb) return true;
  
  // Check for Chinese punctuation variations
  const chineseVariations = [
    [/（/g, '('],
    [/）/g, ')'],
    [/／/g, '/'],
    [/。/g, '.'],
    [/，/g, ','],
    [/：/g, ':'],
    [/；/g, ';']
  ];
  
  let excelVariant = excel;
  let dbVariant = database;
  
  for (const [from, to] of chineseVariations) {
    excelVariant = excelVariant.replace(from, to);
    dbVariant = dbVariant.replace(from, to);
  }
  
  return excelVariant === dbVariant;
}

/**
 * Find character differences between two strings
 */
function findCharacterDifference(excel, database) {
  const differences = [];
  const maxLength = Math.max(excel.length, database.length);
  
  for (let i = 0; i < maxLength; i++) {
    const excelChar = excel[i] || '';
    const dbChar = database[i] || '';
    
    if (excelChar !== dbChar) {
      differences.push(`Position ${i}: '${excelChar}' vs '${dbChar}'`);
    }
  }
  
  if (excel.length !== database.length) {
    differences.push(`Length difference: ${excel.length} vs ${database.length}`);
  }
  
  return differences.join('; ');
}

/**
 * Analyze special characters in field names
 */
function analyzeSpecialCharacters(headers) {
  const analysis = [];
  
  for (const header of headers) {
    if (!header || header.trim() === '') continue;
    
    const fieldAnalysis = {
      field: header,
      hasSlash: header.includes('/') || header.includes('／'),
      hasParentheses: header.includes('(') || header.includes(')') || 
                     header.includes('（') || header.includes('）'),
      hasSpecialPunctuation: /[，。：；！？""'']/g.test(header),
      isMultiLine: header.includes('\\n') || header.includes('\\r'),
      specialCharacters: []
    };
    
    // Extract all special characters
    const specialChars = header.match(/[^\w\s\u4e00-\u9fff]/g) || [];
    fieldAnalysis.specialCharacters = [...new Set(specialChars)];
    
    // Add to analysis if it has special characteristics
    if (fieldAnalysis.hasSlash || fieldAnalysis.hasParentheses || 
        fieldAnalysis.hasSpecialPunctuation || fieldAnalysis.isMultiLine) {
      analysis.push(fieldAnalysis);
    }
  }
  
  return analysis;
}

/**
 * Verify field mapping between Excel and database
 */
function verifyFieldMapping(excelHeaders, databaseColumns, tableName) {
  const cleanExcelHeaders = excelHeaders.filter(h => h && h.trim() !== '');
  const cleanDbColumns = databaseColumns.filter(col => col && col.trim() !== '');
  
  const exactMatches = [];
  const missingFields = [];
  const extraFields = [];
  const characterDifferences = [];
  
  // Check each Excel header against database columns
  for (const excelHeader of cleanExcelHeaders) {
    const exactMatch = cleanDbColumns.find(dbCol => isExactMatch(excelHeader, dbCol));
    
    if (exactMatch) {
      exactMatches.push(excelHeader);
    } else {
      // Look for close matches
      const closeMatch = cleanDbColumns.find(dbCol => isCloseMatch(excelHeader, dbCol));
      
      if (closeMatch) {
        const difference = findCharacterDifference(excelHeader, closeMatch);
        characterDifferences.push({
          excel: excelHeader,
          database: closeMatch,
          difference
        });
      } else {
        missingFields.push(excelHeader);
      }
    }
  }
  
  // Check for extra fields in database
  for (const dbCol of cleanDbColumns) {
    const foundInExcel = cleanExcelHeaders.find(excelHeader => 
      isExactMatch(excelHeader, dbCol) || isCloseMatch(excelHeader, dbCol)
    );
    
    if (!foundInExcel) {
      extraFields.push(dbCol);
    }
  }
  
  // Calculate match percentage
  const totalExcelFields = cleanExcelHeaders.length;
  const matchedFields = exactMatches.length;
  const matchPercentage = totalExcelFields > 0 ? (matchedFields / totalExcelFields) * 100 : 0;
  
  // Determine status
  let status = 'MATCH';
  if (missingFields.length > 0) status = 'MISSING_FIELDS';
  if (extraFields.length > 0) status = 'EXTRA_FIELDS';
  if (characterDifferences.length > 0) status = 'CHARACTER_DIFFERENCES';
  if (matchPercentage === 100 && missingFields.length === 0 && 
      extraFields.length === 0 && characterDifferences.length === 0) {
    status = 'MATCH';
  }
  
  return {
    tableName,
    status,
    exactMatches,
    missingFields,
    extraFields,
    characterDifferences,
    totalExcelFields,
    totalDatabaseFields: cleanDbColumns.length,
    matchPercentage: Math.round(matchPercentage * 100) / 100
  };
}

/**
 * Get all Excel files
 */
function getExcelFiles() {
  const excelDirs = [
    path.join(process.cwd(), 'excel', '06-06'),
    path.join(process.cwd(), 'excel', '06-18'),
    path.join(process.cwd(), 'excel', '06-21')
  ];
  
  const files = [];
  
  for (const dir of excelDirs) {
    if (fs.existsSync(dir)) {
      const dirFiles = fs.readdirSync(dir)
        .filter(f => f.endsWith('.xlsx') || f.endsWith('.xls'))
        .map(f => path.join(dir, f));
      files.push(...dirFiles);
    }
  }
  
  return files;
}

/**
 * Main verification function
 */
function runComprehensiveVerification() {
  console.log('🔍 Starting comprehensive field verification...');
  console.log('='.repeat(80));
  
  const results = [];
  const allSpecialCharacterAnalysis = [];
  
  try {
    // Get database tables
    const dbTables = getDatabaseTables();
    console.log(`📊 Found ${dbTables.length} database tables`);
    
    // Get Excel files
    const excelFiles = getExcelFiles();
    console.log(`📁 Found ${excelFiles.length} Excel files`);
    
    // Process each Excel file
    for (const excelFile of excelFiles) {
      console.log(`\\n📄 Processing: ${path.basename(excelFile)}`);
      
      const parsedFile = parseExcelFile(excelFile);
      if (!parsedFile) continue;
      
      for (const sheet of parsedFile.sheets) {
        // Find corresponding database table
        const tableMatch = dbTables.find(table => {
          const tableNumber = table.name.replace('table_c', '');
          return sheet.sheetName.includes(tableNumber) || 
                 sheet.sheetName.includes(`C${tableNumber}`);
        });
        
        if (!tableMatch) {
          console.log(`    ⚠️  No matching table found for sheet: ${sheet.sheetName}`);
          continue;
        }
        
        console.log(`    🔗 Verifying: ${sheet.sheetName} -> ${tableMatch.name}`);
        
        // Get database columns
        const dbColumns = getTableColumns(tableMatch.name);
        
        // Verify mapping
        const result = verifyFieldMapping(
          sheet.headers, 
          dbColumns, 
          `${sheet.sheetName} -> ${tableMatch.name}`
        );
        
        results.push(result);
        
        // Analyze special characters
        const specialChars = analyzeSpecialCharacters(sheet.headers);
        allSpecialCharacterAnalysis.push(...specialChars);
        
        // Print immediate results
        if (result.status === 'MATCH') {
          console.log(`    ✅ Perfect match: ${result.exactMatches.length} fields`);
        } else {
          console.log(`    ⚠️  ${result.status}: ${result.matchPercentage}% match`);
          if (result.missingFields.length > 0) {
            console.log(`       Missing: ${result.missingFields.length} fields`);
          }
          if (result.extraFields.length > 0) {
            console.log(`       Extra: ${result.extraFields.length} fields`);
          }
          if (result.characterDifferences.length > 0) {
            console.log(`       Character differences: ${result.characterDifferences.length}`);
          }
        }
      }
    }
    
    // Generate summary
    console.log('\\n' + '='.repeat(80));
    console.log('📋 COMPREHENSIVE VERIFICATION RESULTS');
    console.log('='.repeat(80));
    
    const totalTables = results.length;
    const perfectMatches = results.filter(r => r.status === 'MATCH').length;
    const partialMatches = results.filter(r => 
      r.status !== 'MATCH' && r.matchPercentage > 0
    ).length;
    const failures = results.filter(r => r.matchPercentage === 0).length;
    
    const totalMatchPercentage = results.length > 0 
      ? results.reduce((sum, r) => sum + r.matchPercentage, 0) / results.length
      : 0;
    
    console.log(`\\n📊 SUMMARY:`);
    console.log(`   Total Tables Verified: ${totalTables}`);
    console.log(`   Perfect Matches: ${perfectMatches} (${((perfectMatches / totalTables) * 100).toFixed(1)}%)`);
    console.log(`   Partial Matches: ${partialMatches} (${((partialMatches / totalTables) * 100).toFixed(1)}%)`);
    console.log(`   Failures: ${failures} (${((failures / totalTables) * 100).toFixed(1)}%)`);
    console.log(`   Overall Match Percentage: ${totalMatchPercentage.toFixed(2)}%`);
    
    // Detailed results
    console.log('\\n🔍 DETAILED ISSUES:');
    
    // Missing fields
    const missingFieldResults = results.filter(r => r.missingFields.length > 0);
    if (missingFieldResults.length > 0) {
      console.log(`\\n❌ MISSING FIELDS (${missingFieldResults.length} tables):`);
      missingFieldResults.forEach(result => {
        console.log(`\\n   ${result.tableName}:`);
        result.missingFields.forEach(field => {
          console.log(`     • "${field}"`);
        });
      });
    }
    
    // Extra fields
    const extraFieldResults = results.filter(r => r.extraFields.length > 0);
    if (extraFieldResults.length > 0) {
      console.log(`\\n⚠️  EXTRA FIELDS (${extraFieldResults.length} tables):`);
      extraFieldResults.forEach(result => {
        console.log(`\\n   ${result.tableName}:`);
        result.extraFields.forEach(field => {
          console.log(`     • "${field}"`);
        });
      });
    }
    
    // Character differences
    const charDiffResults = results.filter(r => r.characterDifferences.length > 0);
    if (charDiffResults.length > 0) {
      console.log(`\\n🔍 CHARACTER DIFFERENCES (${charDiffResults.length} tables):`);
      charDiffResults.forEach(result => {
        console.log(`\\n   ${result.tableName}:`);
        result.characterDifferences.forEach(diff => {
          console.log(`     Excel: "${diff.excel}"`);
          console.log(`     Database: "${diff.database}"`);
          console.log(`     Difference: ${diff.difference}`);
          console.log('');
        });
      });
    }
    
    // Special character analysis
    if (allSpecialCharacterAnalysis.length > 0) {
      console.log('\\n🔤 SPECIAL CHARACTER ANALYSIS:');
      
      const withSlashes = allSpecialCharacterAnalysis.filter(a => a.hasSlash);
      if (withSlashes.length > 0) {
        console.log(`\\n   Fields with slashes (${withSlashes.length}):`);
        withSlashes.forEach(analysis => {
          console.log(`     • "${analysis.field}"`);
        });
      }
      
      const withParentheses = allSpecialCharacterAnalysis.filter(a => a.hasParentheses);
      if (withParentheses.length > 0) {
        console.log(`\\n   Fields with parentheses (${withParentheses.length}):`);
        withParentheses.forEach(analysis => {
          console.log(`     • "${analysis.field}"`);
        });
      }
      
      const withSpecialPunctuation = allSpecialCharacterAnalysis.filter(a => a.hasSpecialPunctuation);
      if (withSpecialPunctuation.length > 0) {
        console.log(`\\n   Fields with special punctuation (${withSpecialPunctuation.length}):`);
        withSpecialPunctuation.forEach(analysis => {
          console.log(`     • "${analysis.field}" - Characters: ${analysis.specialCharacters.join(', ')}`);
        });
      }
    }
    
    // Save detailed report
    const timestamp = new Date().toISOString().replace(/[:.]/g, '-');
    const reportPath = path.join(process.cwd(), `field-verification-report-${timestamp}.json`);
    
    const report = {
      timestamp: new Date().toISOString(),
      summary: {
        totalTables,
        perfectMatches,
        partialMatches,
        failures,
        overallMatchPercentage: totalMatchPercentage
      },
      results,
      specialCharacterAnalysis: allSpecialCharacterAnalysis
    };
    
    fs.writeFileSync(reportPath, JSON.stringify(report, null, 2));
    console.log(`\\n📊 Detailed report saved to: ${reportPath}`);
    
    console.log('\\n' + '='.repeat(80));
    console.log('✅ VERIFICATION COMPLETE');
    console.log('='.repeat(80));
    
    // Check for critical issues
    const criticalIssues = results.filter(r => r.matchPercentage < 50);
    if (criticalIssues.length > 0) {
      console.log(`\\n⚠️  CRITICAL ISSUES: ${criticalIssues.length} tables have less than 50% field matching`);
      return false;
    }
    
    return true;
    
  } catch (error) {
    console.error('💥 Error during verification:', error);
    return false;
  } finally {
    db.close();
  }
}

// Run verification
if (require.main === module) {
  const success = runComprehensiveVerification();
  process.exit(success ? 0 : 1);
}

module.exports = {
  runComprehensiveVerification,
  verifyFieldMapping,
  analyzeSpecialCharacters
};